﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using FarPoint.Win.Spread;
using Nissen_Model.Dto;
using Nissen_Resources;

namespace Nissen_Manage.Forms
{
	public partial class ManageTestCodeImport : Form
	{
		public ManageTestCodeImport()
		{
			InitializeComponent();
		}

		private Thread t;
		private void btnSeach_Click(object sender, EventArgs e)
		{
			openFileDialog1.Filter = "Excel(*.xls)|*.xls";
			openFileDialog1.FileName = "";
			if (this.openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
			{
				this.txtFilePath.Text = openFileDialog1.FileName;
			}
		}

		private void btnImport_Click(object sender, EventArgs e)
		{
			try
			{
				if (this.txtFilePath.Text.Trim() == "")
				{
					MessageBox.Show(ResourceCommon.GetResource("PleasChangeFile"));
					return;
				}
				ControlPage(true);
				t = new Thread(delegate() { ImportDataProcess(this.txtFilePath.Text); });
				t.Start();
			}
			catch (Exception ex)
			{

				MessageBox.Show(ex.Message);
				ControlPage(false);
			}
		}

		private void ImportDataProcess(string filePath)
		{

			var data = TestReadFile(filePath);

			ImportDataInDB(data);

			this.Invoke(new EventHandler(delegate
			{
				this.ShowMessage(ResourceCommon.GetResource("ImportSuccess"));
				ControlPage(false);
			}));


		}

		/// <summary>
		/// 消息提示
		/// </summary>
		/// <param name="msg"></param>
		public void ShowMessage(string msg)
		{
			MessageBox.Show(msg);
		}

		private void ControlPage(bool flag)
		{
			if (flag)
			{
				lblLoading.Text = ResourceCommon.GetResource("ImportLoading");
				btnSeach.Enabled = false;
				btnImport.Enabled = false;
			}
			else
			{
				lblLoading.Text = "";
				btnSeach.Enabled = true;
				btnImport.Enabled = true;
			}
		}
		private IList<TestCodeDto> TestReadFile(string filePath)
		{
			IList<TestCodeDto> testCodeDtos = new List<TestCodeDto>();
			try
			{

				FpSpread spread = new FpSpread();
				//var excelPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "ExcelTemplate\\ExcelTemplate.xls";
				spread.OpenExcel(filePath);

				SheetView sheetView = spread.Sheets[0];

				int i = 1;

				while (sheetView.Cells[i, 0].Value != null)
				{
					TestCodeDto testCodeDto = new TestCodeDto();
					testCodeDto.TEST_CODE = sheetView.Cells[i, 0].Value.ToString();
					testCodeDto.COURSE_LEVEL_ID = sheetView.Cells[i, 1].Value.ToString();
					testCodeDto.KYOKA_CD = sheetView.Cells[i, 2].Value.ToString();
					testCodeDto.UNIT_NO =int.Parse( sheetView.Cells[i, 3].Value.ToString());
					testCodeDtos.Add(testCodeDto);
					i++;
				}


			}
			catch (Exception ex)
			{

				if (Thread.CurrentThread.ThreadState == ThreadState.AbortRequested)
				{
					return null;

				}
				this.Invoke(new EventHandler(delegate
				{
					this.ShowMessage(ex.Message);
					ControlPage(false);
				}));
			}
			return testCodeDtos;
		}

		private void ImportDataInDB(IList<TestCodeDto> geresults)
		{
			int i = 0;
			string connectionString = ConfigurationManager.AppSettings["DbHelperConnectionStringReport"];
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				SqlTransaction sqlTransaction = connection.BeginTransaction();
				try
				{
					SqlCommand sqlCommand1 = new SqlCommand();
					sqlCommand1.Connection = connection;
					sqlCommand1.Transaction = sqlTransaction;
					sqlCommand1.CommandText = "DELETE FROM TEST_CODE";
					sqlCommand1.ExecuteNonQuery();

					foreach (var geresult in geresults)
					{
						i++;
						SqlCommand sqlCommand = new SqlCommand();

						sqlCommand.Connection = connection;
						sqlCommand.Transaction = sqlTransaction;
						sqlCommand.CommandText =
							@"INSERT INTO [TEST_CODE]
									   ([DATE]
									   ,[TEST_CODE]
									   ,[COURSE_LEVEL_ID]
									   ,[KYOKA_CD]
									   ,[UNIT_NO])
									 VALUES
										   (null
										   ,@TEST_CODE
										   ,@COURSE_LEVEL_ID
										   ,@KYOKA_CD
										   ,@UNIT_NO)";

						sqlCommand.Parameters.Add(new SqlParameter("@TEST_CODE", geresult.TEST_CODE));
						sqlCommand.Parameters.Add(new SqlParameter("@COURSE_LEVEL_ID", geresult.COURSE_LEVEL_ID));
						sqlCommand.Parameters.Add(new SqlParameter("@KYOKA_CD", geresult.KYOKA_CD));
						sqlCommand.Parameters.Add(new SqlParameter("@UNIT_NO", geresult.UNIT_NO));
						sqlCommand.ExecuteNonQuery();
					}
					sqlTransaction.Commit();
				}
				catch (Exception)
				{

					sqlTransaction.Rollback();
					throw;
				}

			}
		}

		private void ManageFileImport_FormClosing(object sender, FormClosingEventArgs e)
		{
			if (t != null)
			{
				t.Abort();
			}
		}
	}
}
